﻿using CountryDepth.Library.BusinessLogic;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace CountryDepth.Library.DataAccess
{
    public static partial class Main
    {

        #region Public Methods

        public static BLUser GetUserById(int userId)
        {
            try
            {
                command.CommandText = "usp_SelectUserById";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Clear();

                command.Parameters.Add(new SqlParameter("@UserId", userId));
                dtMaster = new DataTable();

                adapter.Fill(dtMaster);

                if (dtMaster != null && dtMaster.Rows.Count > 0)
                {
                    DataRow drUser = dtMaster.Rows[0];

                    BLUser user = new BLUser();
                    user = FillUser(drUser);

                    return user;
                }
                else
                    return null;

            }
            catch (SqlException sqlExc)
            {
                return null;
            }
            catch (Exception exc)
            {
                return null;
            }

        }

        public static bool SaveUser(BLUser user)
        {
            if (user.UserId == 0)
            {
                // New User 
                return CreateUser(user);
            }
            else
            {
                // Update User 
                return UpdateUser(user);
            }

        }

        public static List<Item> GetAllUsers()
        {
            try
            {
                command.CommandText = "usp_SelectAllUsers";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Clear();

                dtMaster = new DataTable();

                adapter.Fill(dtMaster);

                if (dtMaster != null && dtMaster.Rows.Count > 0)
                {
                    List<Item> lstUsers = new List<Item>();
                    foreach (DataRow dr in dtMaster.Rows)
                    {
                        Item user = new Item();
                        user.Id = Convert.ToInt32(dr["UserId"]);
                        user.Value = dr["FullName"].ToString();

                        lstUsers.Add(user);
                    }

                    return lstUsers;
                }
                else
                    return null;

            }
            catch (SqlException sqlExc)
            {
                return null;
            }
            catch (Exception exc)
            {
                return null;
            }
        }


        #endregion

        #region Private Methods

        private static BLUser FillUser(DataRow dr)
        {
            BLUser currentUser = new BLUser();

            currentUser.UserId = Convert.ToInt32(dr["UserId"]);
            currentUser.FullName = dr["FullName"].ToString();

            currentUser.UserName = dr["UserName"].ToString();
            currentUser.Password = dr["Password"].ToString();

            currentUser.Telephone = dr["Telephone"].ToString();
            currentUser.Mobile = dr["Mobile"].ToString();
            currentUser.Email = dr["Email"].ToString();
            currentUser.Address = dr["Address"].ToString();

            currentUser.InitialAnnualBalance = Convert.ToInt32(dr["InitialAnnualBalance"]);
            currentUser.InitialCasualBalance = Convert.ToInt32(dr["InitialCasualBalance"]);
            currentUser.WorkingHoursPerDay = Convert.ToInt32(dr["NumberOfWorkingHoursPerDay"]);

            currentUser.IsFriWork = dr["IsFriWork"] == DBNull.Value ? false : Convert.ToBoolean(dr["IsFriWork"]);
            currentUser.IsSatWork = dr["IsSatWork"] == DBNull.Value ? false : Convert.ToBoolean(dr["IsSatWork"]);
            currentUser.IsSunWork = dr["IsSunWork"] == DBNull.Value ? false : Convert.ToBoolean(dr["IsSunWork"]);
            currentUser.IsMonWork = dr["IsMonWork"] == DBNull.Value ? false : Convert.ToBoolean(dr["IsMonWork"]);
            currentUser.IsTueWork = dr["IsTueWork"] == DBNull.Value ? false : Convert.ToBoolean(dr["IsTueWork"]);
            currentUser.IsWedWork = dr["IsWedWork"] == DBNull.Value ? false : Convert.ToBoolean(dr["IsWedWork"]);
            currentUser.IsThuWork = dr["IsThuWork"] == DBNull.Value ? false : Convert.ToBoolean(dr["IsThuWork"]);

            currentUser.Manager = dr["ManagerId"] == DBNull.Value ? new Item() : new Item { Id = Convert.ToInt32(dr["ManagerId"]), Value = dr["ManagerName"].ToString() };

            return currentUser;

        }

        private static bool CreateUser(BLUser user)
        {
            try
            {
                command.CommandText = "usp_InsertUser";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Clear();

                command.Parameters.Add(new SqlParameter("@FullName", user.FullName));
                command.Parameters.Add(new SqlParameter("@Username", user.UserName));
                command.Parameters.Add(new SqlParameter("@Password", user.Password));
                command.Parameters.Add(new SqlParameter("@Telephone", user.Telephone));
                command.Parameters.Add(new SqlParameter("@Mobile", user.Mobile));
                command.Parameters.Add(new SqlParameter("@Email", user.Email));
                command.Parameters.Add(new SqlParameter("@Address", user.Address));

                if (user.Manager.Id == 0)
                    command.Parameters.Add(new SqlParameter("@ManagerId", DBNull.Value));
                else
                    command.Parameters.Add(new SqlParameter("@ManagerId", user.Manager.Id));

                command.Parameters.Add(new SqlParameter("@InitialAnnualBalance", user.InitialAnnualBalance));
                command.Parameters.Add(new SqlParameter("@InitialCasualBalance", user.InitialCasualBalance));
                command.Parameters.Add(new SqlParameter("@IsFriWork", user.IsFriWork));
                command.Parameters.Add(new SqlParameter("@IsSatWork", user.IsSatWork));
                command.Parameters.Add(new SqlParameter("@IsSunWork", user.IsSunWork));
                command.Parameters.Add(new SqlParameter("@IsMonWork", user.IsMonWork));
                command.Parameters.Add(new SqlParameter("@IsTueWork", user.IsTueWork));
                command.Parameters.Add(new SqlParameter("@IsWedWork", user.IsWedWork));
                command.Parameters.Add(new SqlParameter("@IsThuWork", user.IsThuWork));

                command.Parameters.Add(new SqlParameter("@NumberOfWorkingHoursPerDay", user.WorkingHoursPerDay));

                if (connection.State != ConnectionState.Open)
                    connection.Open();

                command.ExecuteScalar();

                if (connection.State != ConnectionState.Closed)
                    connection.Close();

                return true;
            }
            catch (SqlException sqlExc)
            {

                if (connection.State != ConnectionState.Closed)
                    connection.Close();

                return false;
            }
            catch (Exception exc)
            {

                if (connection.State != ConnectionState.Closed)
                    connection.Close();

                return false;
            }
        }

        private static bool UpdateUser(BLUser user)
        {
            try
            {
                command.CommandText = "usp_UpdateUser";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Clear();

                command.Parameters.Add(new SqlParameter("@UserId", user.UserId));
                command.Parameters.Add(new SqlParameter("@FullName", user.FullName));
                command.Parameters.Add(new SqlParameter("@Username", user.UserName));
                command.Parameters.Add(new SqlParameter("@Password", user.Password));
                command.Parameters.Add(new SqlParameter("@Telephone", user.Telephone));
                command.Parameters.Add(new SqlParameter("@Mobile", user.Mobile));
                command.Parameters.Add(new SqlParameter("@Email", user.Email));
                command.Parameters.Add(new SqlParameter("@Address", user.Address));

                if (user.Manager.Id == 0)
                    command.Parameters.Add(new SqlParameter("@ManagerId", DBNull.Value));
                else
                    command.Parameters.Add(new SqlParameter("@ManagerId", user.Manager.Id));

                command.Parameters.Add(new SqlParameter("@InitialAnnualBalance", user.InitialAnnualBalance));
                command.Parameters.Add(new SqlParameter("@InitialCasualBalance", user.InitialCasualBalance));
                command.Parameters.Add(new SqlParameter("@IsFriWork", user.IsFriWork));
                command.Parameters.Add(new SqlParameter("@IsSatWork", user.IsSatWork));
                command.Parameters.Add(new SqlParameter("@IsSunWork", user.IsSunWork));
                command.Parameters.Add(new SqlParameter("@IsMonWork", user.IsMonWork));
                command.Parameters.Add(new SqlParameter("@IsTueWork", user.IsTueWork));
                command.Parameters.Add(new SqlParameter("@IsWedWork", user.IsWedWork));
                command.Parameters.Add(new SqlParameter("@IsThuWork", user.IsThuWork));

                command.Parameters.Add(new SqlParameter("@NumberOfWorkingHoursPerDay", user.WorkingHoursPerDay));


                if (connection.State != ConnectionState.Open)
                    connection.Open();

                command.ExecuteNonQuery();

                if (connection.State != ConnectionState.Closed)
                    connection.Close();

                return true;
            }
            catch (SqlException sqlExc)
            {
                if (connection.State != ConnectionState.Closed)
                    connection.Close();

                return false;
            }
            catch (Exception exc)
            {
                if (connection.State != ConnectionState.Closed)
                    connection.Close();

                return false;
            }
        }
        
        #endregion
                        
    }
}
